Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
dvdbly01
New Contributor II

Conditional WHERE

Hi all,

In order to shorten my code, I loop in a list of file names that are to be loaded identically, except for the "where" clause :

FOR EACH vFichier IN '$(vHistFile_O)','$(vHistFile_P)'

     LOAD

     [...]

     WHERE Condition;

NEXT vFichier

I would like my Condition to depend on the file name.

I have tried different solutions but nothing works and I am running short of imagination!

I have tried :

a)

  WHERE

  IF ('$(vFichier)'='$(vHistFile-O)',

    [LG_ETAT]<>'A',

    IF ('$(vFichier)'='$(vHistFile-P)',

      [LG_HT_LIGNE]+0<>0 AND [LG_ETAT]<>'A',

      TRUE()

    )

  )

  ;

b)

FOR EACH vFichier IN '$(vHistFile_O)','$(vHistFile_P)'

     IF '$(vSrcTest)'='$(vHistFile-O)' THEN

           SET vCond = '[LG_ETAT]<>' & chr(39)& 'A' & chr(39);

    ELSE

        IF '$(vFichier)'='$(vHistFile-P)' THEN

              Set vCond = '[LG_HT_LIGNE]+0<>0 AND [LG_ETAT]<>' & chr(39) & 'A' & chr(39);

        ELSE

            Set vCond = 'TRUE()';

        END IF

    END IF

    LOAD

    [...]

    WHERE [$(vCond)]**

NEXT vFichier

** tried syntaxes: '$(vCond)', vCond, $(Var), [$(vCond)] -> nothing works.

Has anyone any idea as of how to do that?

Thanks

Tags (3)
1 Solution

Accepted Solutions
dvdbly01
New Contributor II

Re: Conditional WHERE

Obviously, I misunderstood the problem.

After some more testing, I found THE working syntax, much simpler:

FOR EACH vFichier IN '$(vHistFile_O)','$(vHistFile_P)'

TMP_LIGCLI:

    LOAD

[...]

WHERE ('$(vFichier)'='$(vHistFile_O)' AND [LG_ETAT]<>'A') OR ('$(vFichier)'='$(vHistFile_P)' AND [LG_HT_LIGNE]+0<>0 AND [LG_ETAT]<>'A');

View solution in original post

1 Reply
dvdbly01
New Contributor II

Re: Conditional WHERE

Obviously, I misunderstood the problem.

After some more testing, I found THE working syntax, much simpler:

FOR EACH vFichier IN '$(vHistFile_O)','$(vHistFile_P)'

TMP_LIGCLI:

    LOAD

[...]

WHERE ('$(vFichier)'='$(vHistFile_O)' AND [LG_ETAT]<>'A') OR ('$(vFichier)'='$(vHistFile_P)' AND [LG_HT_LIGNE]+0<>0 AND [LG_ETAT]<>'A');

View solution in original post