topic Re: SQL Connection and Script Format in App Development

SQL Connection and Script Format

EvanBarrick — Fri, 08 Mar 2019 19:24:17 GMT

I have a table of data that is populated as such:

UserID PropertyName PropertyValue

1234 FristName John

1234 LastName Smith

1234 City Omaha

1234 Street Pine

How can I structure my script so as to get a more traditional table such as below

User ID FirstName LastName City Street

1234 John Smith Omaha Pine

Re: SQL Connection and Script Format

Mauritz_SA — Fri, 08 Mar 2019 21:49:03 GMT

Hi there

I don't have QS open so can't validate syntax, but try something like:

Temp:
Load
*
From YOURTABLE;

Noconcatenate
Newtable:
Load Distinct
UserId
Resident Temp;

Left join (NewTable)
Load
UserId,
PropertyValue As Firstname
Resident Temp
Where
PropertyName = 'Firstname';

Left join (NewTable)
Load
UserId,
PropertyValue As Lastname
Resident Temp
Where
PropertyName = 'Lastname';

Left join (NewTable)
Load
UserId,
PropertyValue As City
Resident Temp
Where
PropertyName = 'City';

Left join (NewTable)
Load
UserId,
PropertyValue As Street
Resident Temp
Where
PropertyName = 'Street';

Drop Table Temp;

If you will have a dynamic number of PropertyNames then you can load a list of distinct PropertyNames into a table and loop through them and left joining the PropertyName to the existing table every time.

Good luck!
Mauritz

Re: SQL Connection and Script Format

jayanttibhe — Fri, 08 Mar 2019 22:55:22 GMT

Hi,

You need to use Generic Load and then Combine the resultant Tables. You can follow this simple code and execute in DEBUG mode to understand more.

Temp:
LOAD * INLINE [
    UserID, PropertyName, PropertyValue
    1234, City, Omaha
    1234, FristName, John
    1234, LastName, Smith
    1234, Street, Pine
];

Test:
LOAD 
Distinct UserID
Resident Temp;

Test_Gen:
Generic Load *
Resident Temp;

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTable=TableName($(i)); 
  IF WildMatch('$(vTable)', 'Test_Gen.*') THEN 
    LEFT JOIN (Test) LOAD * RESIDENT    [$(vTable)]; 
    DROP TABLE  [$(vTable)]; 
  ENDIF 
NEXT i

DROP Table Temp;
vTable=;

Exit SCRIPT;

Re: SQL Connection and Script Format

bing_jville — Sun, 10 Mar 2019 18:02:46 GMT

Qlik has Crosstable function
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/crosstable.htm